﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WXY.Course.Model;

namespace WXY.Course.DAL
{
    /// <summary>
    /// 菜单
    /// </summary>
    public class MenuDAL
    {
        private static SqlHelper sqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CourseDB"].ConnectionString);

        private const string SQL_INSERT = @"INSERT INTO TMenu(Name,[Order],Url,TargetBlank)
                                            values(@Name,@Order,@Url,@TargetBlank)";

        private const string SQL_UPDATE = @"UPDATE TMenu SET
                                            Name=@Name,[Order]=@Order,Url=@Url,TargetBlank=@TargetBlank
                                            WHERE Code=@Code";

        private const string SQL_DELETE = @"DELETE FROM TMenu
                                            WHERE Code=@Code";

        private const string SQL_GET = "select * from TMenu where Code=@Code";

        private const string SQL_GETLIST = "select * from TMenu order by [Order]";

        private const string SQL_GETPAGELIST = @"select top (@PageSize) * from (
                            select row_number() over(order by code desc) as rownumber,* from TMenu 
                            where Name like @Name
                         )
                         t where rownumber > (@PageIndex -1 ) * @PageSize";

        private const string SQL_GETPAGECOUNT = @"select count(*) from TMenu 
                            where Name like @Name";

        public static int Insert(MenuInfo menu)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Name", menu.Name),
                new SqlParameter("@Order", menu.Order),
                new SqlParameter("@Url", menu.Url),
                new SqlParameter("@TargetBlank", menu.TargetBlank)
            };

            return sqlHelper.ExecuteNonQuery(SQL_INSERT, CommandType.Text, parameters);
        }

        public static int Update(MenuInfo menu)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", menu.Code),
                new SqlParameter("@Name", menu.Name),
                new SqlParameter("@Order", menu.Order),                
                new SqlParameter("@Url", menu.Url),
                new SqlParameter("@TargetBlank", menu.TargetBlank)
            };

            return sqlHelper.ExecuteNonQuery(SQL_UPDATE, CommandType.Text, parameters);
        }

        public static int Delete(int code)
        {
            SqlParameter[] parameters ={
                new SqlParameter("@Code", code)
            };

            return sqlHelper.ExecuteNonQuery(SQL_DELETE, CommandType.Text, parameters);
        }

        /// <summary>
        /// 获取指定Code的模块信息
        /// </summary>
        /// <returns></returns>
        public static MenuInfo Get(int code)
        {
            SqlParameter parameter = new SqlParameter("@Code", code);

            return sqlHelper.ExecuteQuery(SQL_GET, CommandType.Text, (reader) =>
            {
                MenuInfo module = null;
                while (reader.Read())
                {
                    module = new MenuInfo();
                    module.Code = Convert.ToInt32(reader["Code"]);
                    module.Name = reader["Name"].ToString();
                    module.Url = reader["Url"].ToString();
                    module.Order = Convert.ToInt32(reader["Order"]);
                    module.TargetBlank = Convert.ToBoolean(reader["TargetBlank"]);
                }
                return module;
            }, parameter);
        }

        /// <summary>
        /// 获取模块列表
        /// </summary>
        public static IList<MenuInfo> GetList()
        {
            return sqlHelper.ExecuteQuery(SQL_GETLIST, CommandType.Text, (reader) =>
            {
                IList<MenuInfo> list = new List<MenuInfo>();
                while (reader.Read())
                {
                    MenuInfo module = new MenuInfo();
                    module.Code = Convert.ToInt32(reader["Code"]);
                    module.Name = reader["Name"].ToString();
                    module.Url = reader["Url"].ToString();
                    module.Order = Convert.ToInt32(reader["Order"]);
                    module.TargetBlank = Convert.ToBoolean(reader["TargetBlank"]);
                    list.Add(module);
                }
                return list;
            }, null);
        }

        public static IList<MenuInfo> GetPageList(string name, PageParams pageParams)
        {
            SqlParameter[] parameter = {
                new SqlParameter("@Name", string.Format("%{0}%",name)),
                new SqlParameter("@PageIndex",  pageParams.PageIndex),
                new SqlParameter("@PageSize",  pageParams.PageSize),
            };
            pageParams.RecordCount = GetPageCount(name, pageParams);
            return sqlHelper.ExecuteQuery(SQL_GETPAGELIST, CommandType.Text, (reader) =>
            {
                IList<MenuInfo> list = new List<MenuInfo>();
                while (reader.Read())
                {
                    MenuInfo module = new MenuInfo();
                    module.Code = Convert.ToInt32(reader["Code"]);
                    module.Name = reader["Name"].ToString();
                    module.Url = reader["Url"].ToString();
                    module.Order = Convert.ToInt32(reader["Order"]);
                    module.TargetBlank = Convert.ToBoolean(reader["TargetBlank"]);
                    list.Add(module);
                }
                return list;
            }, parameter);
        }
        public static int GetPageCount(string name, PageParams pageParams)
        {
            SqlParameter[] parameter = {
                new SqlParameter("@Name", string.Format("%{0}%",name))
            };
            object o = sqlHelper.ExecuteScalar(SQL_GETPAGECOUNT, CommandType.Text, parameter);

            return Convert.ToInt32(o);
        }
    }
}
